import argparse
import pymysql
import os

# 写入到文件
# cursor
# values
# 批量读取ftpdata_path文件内容
def inert_into_db(db, cursor, table_name, values, number):
        
    # 获取values中的id,phone,
    ids = tuple(values.keys())
    sql = "SELECT id,phone FROM mobile_encrypt WHERE id IN %s"
    # 执行SQL语句，并将结果中的id、phone、date保存到文件中
    cursor.execute(sql, (ids,))
    results = cursor.fetchall()
    
    # 批量保存id,phone,date到table_name表中
    dataValues = []
    for result in results:
        id, phone = result
        # 获取values中id对应的date,并只获取月份202401
        month = values[id]
        # 将id,phone,date插入到table_name表中
        dataValues.append((id, phone, month, number))
    
    # 创建SQL语句
    sql = f"INSERT INTO {table_name}(id, phone, month, no) VALUES (%s, %s, %s, %s)"
    try:
        # 执行SQL语句
        cursor.executemany(sql, dataValues)
        # 提交到数据库执行
        db.commit()
    except pymysql.Error as e:
        # 打印数据库异常
        print(e)
        
        # 如果发生错误则回滚
        db.rollback()
            


def main():
    parser = argparse.ArgumentParser()
    # parser.add_argument("--phone", help="Phone number")
    parser.add_argument("--type", help="the model type's sign, such as 'zaixin_model_hq' or 'zaixin_model_zx' or 'zaixin_model_zf'")
    parser.add_argument("--number", help="批次号，如20240201")
    args = parser.parse_args()
    
    type = args.type
    if type is None:
        print("please input the type")
        return
    
    number = args.number
    if number is None:
        print("please input the number")
        return
    
    ftpdataDir = "/data/dev10086/ftpdata/"
    if type == "zx":
        fileName = f"zaixin_zhuangxiu_rsltout_{number}.txt"
        table_name = "data_zhuangxiu"
        ftpdata_path = ftpdataDir + fileName

    elif type == "hq":
        fileName = f"zaixin_hunqing_rsltout_{number}.txt"
        table_name = "data_hunqing"
        ftpdata_path = ftpdataDir + fileName

    elif type == "zf":
        fileName = f"zaixin_zhifu_rsltout_{number}.txt"
        table_name = "data_zhifu"
        ftpdata_path = ftpdataDir + fileName

    elif type == "jy":
        fileName = f"zaixin_jiaoyu_rsltout_{number}.txt"
        table_name = "data_jiaoyu"
        ftpdata_path = ftpdataDir + fileName

    elif type == "fl":
        fileName = f"zaixin_falv_rsltout_{number}.txt"
        table_name = "data_falv"
        ftpdata_path = ftpdataDir + fileName

    else:
        print("please input the correct type")
        return
    
    # 根据文件判断批次号是否存在
    if not os.path.exists(ftpdata_path):
        print("number is not correct, please input the correct number")
        return
        
    # 创建数据库连接
    db = pymysql.connect(host='localhost', user='root', password='lcz19860109', db='dev10086', charset='utf8')
    # 创建游标对象
    cursor = db.cursor()
    batch_size = 1000  # 每次插入1000行
    
    # 从table_name表中获取最后一行的id
    # sql = f"SELECT id FROM {table_name} ORDER BY id DESC LIMIT 1"
    # cursor.execute(sql)
    # last_line = cursor.fetchone()

    with open(ftpdata_path, 'r') as f:
        values = {}
    
        for line in f:
            id, date = line.strip().split('|')
            id = int(id.split('_')[1])
            # values 添加 {id:date}
            values[id] = date
            
            if len(values) >= batch_size:
                # 查询对应的phone并保存到
                inert_into_db(db, cursor, table_name, values, number)  
                values.clear()
                
        if values:
            # insert_into_db(values)
            inert_into_db(db, cursor, table_name, values, number)
            values.clear()
            
    # 关闭数据库连接
    db.close()
    # fileName 转换成phone文件完成，保存到/data/dev10086/phonedata/目录下的文件名为fileName
    print("fileName 转换成phone文件完成，保存到/data/dev10086/phonedata/目录下的文件名为fileName")
    
if __name__ == '__main__':
    main()